package org.firebug.spring.boot.ssj.repository.dao;

import org.firebug.spring.boot.ssj.repository.po.UserPO;
import org.firebug.spring.boot.ssj.repository.qo.UserQO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

@Repository
public class UserDAO {
    private static Logger logger = LoggerFactory.getLogger("sql");
    private static RowMapper<UserPO> mapper = BeanPropertyRowMapper.newInstance(UserPO.class);
    @Resource
    private JdbcTemplate jdbcTemplate;

    public int insertAutoIncrement(UserPO po) {
        String sql = "insert into t_user (name) values (?)";
        Object[] args = {po.getName()};
        logger.info("{} {}", sql, args);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int affect = jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, po.getName());
            return ps;
        }, keyHolder);
        po.setId(keyHolder.getKey().longValue());
        return affect;
    }

    public <T extends Number> int deleteLogicalById(T... id) {
        if (id == null || id.length == 0) {
            return 0;
        }
        String idStr = StringUtils.arrayToDelimitedString(id, ",");
        String sql = "update t_user set is_deleted=1 where id in (" + idStr + ")";
        logger.info(sql);
        return jdbcTemplate.update(sql);
    }

    public <T extends Number> int deletePhysicalById(T... id) {
        if (id == null || id.length == 0) {
            return 0;
        }
        String idStr = StringUtils.arrayToDelimitedString(id, ",");
        String sql = "delete from t_user where id in (" + idStr + ")";
        logger.info(sql);
        return jdbcTemplate.update(sql);
    }

    public int updateById(UserPO po) {
        List<String> columns = new LinkedList<>();
        List<Object> args = new LinkedList<>();
        if (!StringUtils.isEmpty(po.getName())) {
            columns.add("name=?");
            args.add(po.getName());
        }
        if (columns.isEmpty()) {
            return 0;
        }
        String sql = String.format("update t_user set %s where id=%s", StringUtils.collectionToDelimitedString(columns, ","), po.getId());
        logger.info("{} {}", sql, args);
        return jdbcTemplate.update(sql, args.toArray());
    }

    public <T extends Number> List<UserPO> selectById(T... id) {
        if (id == null || id.length == 0) {
            return Collections.EMPTY_LIST;
        }
        String idStr = StringUtils.arrayToDelimitedString(id, ",");
        String sql = "select * from t_user where id in (" + idStr + ")";
        logger.info(sql);
        return jdbcTemplate.query(sql, mapper);
    }

    public int selectCountByQO(UserQO qo) {
        StringBuilder sql = new StringBuilder();
        List<Object> args = new LinkedList<>();
        sql.append("select count(1) from t_user");
        this.appendWhere(sql, args, qo);
        logger.info("{} {}", sql, args);
        return jdbcTemplate.queryForObject(sql.toString(), args.toArray(), Integer.class);
    }

    public List<UserPO> selectByQO(UserQO qo, int offset, int limit) {
        StringBuilder sql = new StringBuilder();
        List<Object> args = new LinkedList<>();
        sql.append("select id from t_user");
        this.appendWhere(sql, args, qo);
        sql.append(" limit " + offset + "," + limit);
        logger.info("{} {}", sql, args);
        List<Long> id = jdbcTemplate.queryForList(sql.toString(), args.toArray(), Long.class);
        return this.selectById(id.toArray(new Long[id.size()]));
    }

    private void appendWhere(StringBuilder sql, List<Object> args, UserQO qo) {
        sql.append(" where is_deleted=0");
        if (!StringUtils.isEmpty(qo.getName())) {
            sql.append(" and name like ?");
            args.add("%" + qo.getName() + "%");
        }
    }

}